package com.example.managementjava.Student;

import com.example.managementjava.DB_message;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DB_Stu {
    private static String url = DB_message.getUrl();
    private static String user= DB_message.getUser();
    private static String pswd = DB_message.getPswd();
    // 全部学生
    public static List<Student> selectAlllist(){
        List<Student> list= new ArrayList<>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, user, pswd);
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("select  * from students");
            while (resultSet.next()){
                String sid = resultSet.getString(1);
                String name = resultSet.getString(2);
                String gender = resultSet.getString(3);
                String college = resultSet.getString(4);
                String professional = resultSet.getString(5);
                String class1 = resultSet.getString(6);
                Student student = new Student(sid,name,gender,college,professional,class1);
//                System.out.println(student.toString());
                list.add(student);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    // 查找
    public static List<Student> selectstudentlist(String msg){
        List<Student> list= new ArrayList<>();
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");//加载Driver驱动
            //数据库账户
            String user="root";
            //数据库密码
            String pswd="123456";
            //建立好java连接mysql的通路
            connection = DriverManager.getConnection(url, user, pswd);
            //在通路上建立一个车
            statement = connection.createStatement();
            //返回值是一个jdbc的数据结构，结果集
            String sql = "select * from students where " + msg;
            System.out.println(sql);
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                String sid = resultSet.getString(1);//从1开始或加字符串
                String name = resultSet.getString(2);
                String gender = resultSet.getString(3);
                String college = resultSet.getString(4);
                String professional = resultSet.getString(5);
                String class1 = resultSet.getString(6);
                Student student = new Student(sid,name,gender,college,professional,class1);
                list.add(student);
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    //  修改
    public static void editStudent(String msg){
        List<Student> list= new ArrayList<>();
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String user="root";
            String pswd="123456";
            connection = DriverManager.getConnection(url, user, pswd);
            statement = connection.createStatement();
            String sql = "update students  set " +msg;

            System.out.println(sql);
            statement.executeUpdate(sql);
            System.out.println("修改成功");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    // 添加学生
    public static void addStudent(String msg){
        List<Student> list= new ArrayList<>();
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String user="root";
            String pswd="123456";
            connection = DriverManager.getConnection(url, user, pswd);
            statement = connection.createStatement();
            String sql="insert into students(sid,name,gender,college,professional,class) values " +msg;

            System.out.println(sql);
            statement.executeUpdate(sql);
            System.out.println("添加成功");

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    //  删除
    public static void delStudent(String msg){
        List<Student> list= new ArrayList<>();
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String user="root";
            String pswd="123456";
            connection = DriverManager.getConnection(url, user, pswd);
            statement = connection.createStatement();
            String sql = "delete from students where " +msg;

            System.out.println(sql);
            statement.executeUpdate(sql);
            System.out.println("删除成功");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

}
